package org.lzy.pt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.StringUtils;
import org.lzy.pt.bean.Plugin;
import org.lzy.pt.bean.PluginType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class PluginManager {

	private Logger Log = LoggerFactory.getLogger(PluginManager.class);
	
	private static final String LOAD_PROG = "SELECT *,T_CnName FROM oftProg,oftProgType where oftProg.T_Name=? and oftProg.T_Ver=? and oftProg.T_Name=oftProgType.T_Name";
	
	private static final String LOAD_PROG_BY_NAME = "SELECT *,T_CnName FROM oftProg,oftProgType where oftProg.T_Name=? and oftProg.T_Name=oftProgType.T_Name order by oftProg.T_Ver desc";
	
	private static final String PROG_COUNT = "SELECT count(*) FROM oftProg";
	
	private static final String LOAD_PROGLIST_LIMIT = "SELECT *,T_CnName FROM oftProg,oftProgType where oftProg.T_Name=oftProgType.T_Name order by T_DateTime desc LIMIT ?,? ";

	private static final String ADD_PROG = "insert into oftProg(T_Name,T_Ver,T_Path,T_Data,T_DateTime) " +
			" values(?,?,?,?,?)";
	
	private static final String UPDATE_PROG = "update oftProg set T_Path=?, T_Data=? where T_Name=? and T_Ver=?";
	
	private static final String DELETE_PROG = "delete from oftProg where T_Name=? and T_Ver=?";
	
	private static final String LOAD_PLUGIN_TYPE="select * from oftProgType";

	private static PluginManager listManager = new PluginManager();

	public PluginManager() {
	}

	public static PluginManager getInstance() {
		return listManager;
	}
	
	public List<PluginType> getPluginTypeList() throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<PluginType> result = runner.query(connection,LOAD_PLUGIN_TYPE, new BeanListHandler<PluginType>(PluginType.class));
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public List<Plugin> getPluginList(int startIndex, int numResults) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<Plugin> result = runner.query(connection,LOAD_PROGLIST_LIMIT, new BeanListHandler<Plugin>(Plugin.class),
    			new Object[]{startIndex,numResults});
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public List<Plugin> getMainPlugin() throws SQLException {
    	return getPluginByName("Main");
	}
	
	public List<Plugin> getTaskPlugin() throws SQLException {
    	return getPluginByName("Task");
	}
	
	public List<Plugin> getPluginByName(String T_Name) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	List<Plugin> result = runner.query(connection,LOAD_PROG_BY_NAME, new BeanListHandler<Plugin>(Plugin.class),
    			new Object[]{T_Name});
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public Plugin getPlugin(String T_Name,int T_Ver) throws SQLException {
    	QueryRunner runner = new QueryRunner();
    	Connection connection = DbConnectionManager.getConnection();
    	Plugin result = runner.query(connection,LOAD_PROG, new BeanHandler<Plugin>(Plugin.class),
    			new Object[]{T_Name,T_Ver});
    	DbConnectionManager.closeConnection(connection);
    	return result;
	}
	
	public long getCount() {
		Connection connection = null;
		long result = 0 ;
		try {
			QueryRunner runner = new QueryRunner();
			connection = DbConnectionManager.getConnection();
			result = (Long) runner.query(connection, PROG_COUNT,new ScalarHandler());
		} catch (SQLException e) {
		} finally {
			DbConnectionManager.closeConnection(connection);
		}
		return result;
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor) throws SQLException{
		return setParam(pstmt, value, cursor, true);
	}
	
	private int setParam(PreparedStatement pstmt,String value, int cursor,boolean required) throws SQLException{
		cursor ++ ;
		if(value != null){
			pstmt.setString(cursor, value);
		}else{
			pstmt.setString(cursor, null);
		}
		return cursor;
	}
	
	private int setParam(PreparedStatement pstmt,int value, int cursor,boolean required) throws SQLException{
		cursor ++ ;
		pstmt.setInt(cursor, value);
		return cursor;
	}
	
	public void addPlugin(String T_Name,int T_Ver,String T_Path,byte[] T_Data) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(ADD_PROG);
			int i = 0;
			i = setParam(pstmt, T_Name, i);
			i = setParam(pstmt, T_Ver, i);
			i = setParam(pstmt, T_Path, i);
			
			StringBuffer buffer = new StringBuffer();
			for (int j = 0; j < T_Data.length; j++) {
				buffer.append(String.format("%02x", T_Data[j])) ;
			}
			i = setParam(pstmt, buffer.toString(), i);
			i = setParam(pstmt, StringUtils.dateToMillis(new Date()) , i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
	}
	
	public void deletePlugin(String T_Name,int T_Ver) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(DELETE_PROG);
			int i = 0;
			i = setParam(pstmt, T_Name, i);
			i = setParam(pstmt, T_Ver, i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
		
	}
	
	public void updatePlugin(Plugin prog) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(UPDATE_PROG);
			int i = 0;
			
			i = setParam(pstmt, prog.getT_Path(), i);
			i = setParam(pstmt, prog.getT_Data(), i);
			i = setParam(pstmt, prog.getT_Name(), i);
			i = setParam(pstmt, prog.getT_Ver(), i);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw e;
		} finally {
			DbConnectionManager.closeTransactionConnection(con, true);
		}
		
	}
	
	
}
